package studysystem.demo.mapper;
import org.apache.ibatis.annotations.*;
import studysystem.demo.bean.Course;
import studysystem.demo.bean.Permission;
import studysystem.demo.bean.Role;
import java.util.List;
@Mapper
public interface CourseMapper {

    //查询所属大类id
    @Select("select class from course where id = #{id,jdbcType=VARCHAR}")
    int findDir_class(int  id);

    //查询所属大类名字
    @Select("select name from course_class where id = #{id,jdbcType=VARCHAR}")
    String findDir(int  id);

    //查询视频资源是否存在
    @Select("select  url from video where url = #{video_url,jdbcType=VARCHAR}")
    String checkVideo_url(String video_url);

    //查询文档资源是否存在
    @Select("select  url from doc where url = #{doc_url,jdbcType=VARCHAR}")
    String checkDoc_url(String doc_url);

    //添加视频资源
    @Insert("insert into video (course_name, course_dir, url, nandu) values (#{course_name,jdbcType=VARCHAR}," +
            " #{course_dir,jdbcType=VARCHAR}, #{video_url,jdbcType=VARCHAR}, #{nandu,jdbcType=VARCHAR})")
    void addVedio(Course courserEntity);

    //添加视频资源
    @Insert("insert into doc (course_name, course_dir, url, nandu) values (#{course_name,jdbcType=VARCHAR}," +
            " #{course_dir,jdbcType=VARCHAR}, #{doc_url,jdbcType=VARCHAR}, #{nandu,jdbcType=VARCHAR})")
    void addDoc(Course courserEntity);

    //查询所有视频
    @Select("select course_name, course_dir, url, nandu from video ")
    List<Course> findVideoAll();

    //查询所有文档
    @Select("select course_name, course_dir, url, nandu from doc ")
    List<Course> findDocAll();

    //查询所有视频条数
    @Select("select count(*) from video")
    int findVideoCountAll();

    //查询所有文档条数
    @Select("select count(*) from doc")
    int findDocCountAll();

    //查询视频条数 (大类 模糊查询)
    @Select("select count(*) from video where course_dir like CONCAT(CONCAT('%', #{course_dir}), '%')")
    int findAllVideoCountByDir(String course_dir);

    //查询文档条数 (大类 模糊查询)
    @Select("select count(*) from doc where course_dir like CONCAT(CONCAT('%', #{course_dir}), '%')")
    int findAllDocCountByDir(String course_dir);

    //查询视频(大类  模糊查询)
    @Select("select course_name, course_dir, url, nandu from video where course_dir like CONCAT(CONCAT('%', #{course_dir}), '%')")
    List<Course> findAllVideoByDir(String course_dir);

    //查询文档(大类  模糊查询)
    @Select("select course_name, course_dir, url, nandu from doc where course_dir like CONCAT(CONCAT('%', #{course_dir}), '%')")
    List<Course> findAllDocByDir(String course_dir);

    //查询视频条数 (课程名  模糊查询)
    @Select("select count(*) from video where course_name like CONCAT(CONCAT('%', #{course_name}), '%')")
    int findAllVideoCountByName(String course_name);

    //查询文档条数 (课程名  模糊查询)
    @Select("select count(*) from doc where course_name like CONCAT(CONCAT('%', #{course_name}), '%')")
    int findAllDocCountByName(String course_name);

    //查询视频(课程名  模糊查询)
    @Select("select course_name, course_dir, url, nandu  from video where course_name like CONCAT(CONCAT('%', #{course_name}), '%')")
    List<Course> findAllVideoByName(String course_name);

    //查询文档(课程名  模糊查询)
    @Select("select course_name, course_dir, url, nandu  from doc where course_name like CONCAT(CONCAT('%', #{course_name}), '%')")
    List<Course> findAllDocByName(String course_name);

    //查询Video ID BY 课程名
    @Select("select id from video where course_name = #{course_name,jdbcType=VARCHAR}")
    int findVideoIdByName(String course_name);

    //查询Doc ID BY 课程名
    @Select("select id from doc where course_name = #{course_name,jdbcType=VARCHAR}")
    int findDocIdByName(String course_name);

    //查询课程ID
    @Select("select id  from course where name= #{name,jdbcType=VARCHAR}")
    int findCourseID(String name);

    //查询课程名
    @Select("select name  from course where id= #{id,jdbcType=VARCHAR}")
    String findCourseName(int id);

    //studing 查询学习者是否存在该课程
    @Select("select learner_phone  from studing where learner_phone= #{learner_phone,jdbcType=VARCHAR} and course_id=  #{course_id,jdbcType=VARCHAR}")
    String findStudinglearner(@Param("learner_phone")String learner_phone,@Param("course_id")int course_id);

    //查询学习者某门课程video的ID
    @Select("select video_id  from studing where learner_phone= #{learner_phone,jdbcType=VARCHAR} and course_id=#{course_id,jdbcType=VARCHAR}")
    int findVideoIdInStuding(@Param("learner_phone")String learner_phone,@Param("course_id")int course_id);

    //查询学习者某门课程doc的ID
    @Select("select doc_id  from studing where learner_phone= #{learner_phone,jdbcType=VARCHAR} and course_id=#{course_id,jdbcType=VARCHAR}")
    int findDocIdInStuding(@Param("learner_phone")String learner_phone,@Param("course_id")int course_id);

    //学习者添加视频1
    @Insert("insert into Studing (learner_phone, course_id, video_id,video_jd) values (#{learner_phone,jdbcType=VARCHAR}," +
            " #{course_id,jdbcType=VARCHAR}, #{video_id,jdbcType=VARCHAR}, '0')")
    void LearneraddVedio(@Param("learner_phone")String  learner_phone,@Param("course_id")int course_id ,@Param("video_id")int video_id);

    //学习者添加文档1
    @Insert("insert into Studing (learner_phone, course_id, doc_id,doc_jd) values (#{learner_phone,jdbcType=VARCHAR}," +
            " #{course_id,jdbcType=VARCHAR}, #{doc_id,jdbcType=VARCHAR}, '0')")
    void LearneraddDoc(@Param("learner_phone")String  learner_phone,@Param("course_id")int course_id ,@Param("doc_id")int doc_id);

    //学习者添加视频2
    @Update("update studing set video_id = #{video_id,jdbcType=VARCHAR} ,video_jd ='0' where learner_phone = #{learner_phone,jdbcType=VARCHAR} and course_id=#{course_id,jdbcType=VARCHAR}")
    void LearnerUpdateVedio(@Param("learner_phone")String  learner_phone,@Param("course_id")int course_id ,@Param("video_id")int video_id);

    //学习者添加文档2
    @Update("update studing set doc_id = #{doc_id,jdbcType=VARCHAR},doc_jd ='0' where learner_phone = #{learner_phone,jdbcType=VARCHAR} and course_id=#{course_id,jdbcType=VARCHAR}")
    void LearnerUpdateDoc(@Param("learner_phone")String  learner_phone,@Param("course_id")int course_id ,@Param("doc_id")int doc_id);

    //学习者删除选择的视频
    @Update("update studing set video_id='0', video_jd=null where  learner_phone = #{learner_phone,jdbcType=VARCHAR} and video_id=#{video_id,jdbcType=VARCHAR}")
    void LearnerdeleteVideo(@Param("learner_phone")String learner_phone,@Param("video_id")int video_id);

    //学习者删除选择的文档
    @Update("update studing set doc_id='0', doc_jd=null where  learner_phone = #{learner_phone,jdbcType=VARCHAR} and doc_id=#{doc_id,jdbcType=VARCHAR}")
    void LearnerdeleteDoc(@Param("learner_phone")String learner_phone,@Param("doc_id")int doc_id);

    //查询学习者所有视频条数
    @Select("select count(*) from studing where learner_phone =#{learner_phone,jdbcType=VARCHAR} and video_id!=0")
    int findMyVideoCountAll(@Param("learner_phone")String learner_phone);

    //查询学习者所有文档条数
    @Select("select count(*) from studing where learner_phone =#{learner_phone,jdbcType=VARCHAR} and doc_id!=0")
    int findMyDocCountAll(@Param("learner_phone")String learner_phone);

    //查询学习者所有视频
    @Select("select course_id, video_jd ,video_id from studing where learner_phone =#{learner_phone,jdbcType=VARCHAR} and video_id!=0")
    List<Course> findMyVideoAll(@Param("learner_phone")String learner_phone);

    //查询学习者所有文档
    @Select("select course_id, doc_jd ,doc_id from studing where learner_phone =#{learner_phone,jdbcType=VARCHAR} and doc_id!=0")
    List<Course> findMyDocAll(@Param("learner_phone")String learner_phone);

    //查询学习者视频条数 (课程名  模糊查询)
    @Select("select count(*) from studing where learner_phone =#{learner_phone,jdbcType=VARCHAR} and video_id!=0 and course_id= #{course_id,jdbcType=VARCHAR}")
    int findMyAllVideoCountByName(@Param("learner_phone")String learner_phone,@Param("course_id")int course_id);

    //查询学习者文档条数 (课程名  模糊查询)
    @Select("select count(*) from studing where learner_phone =#{learner_phone,jdbcType=VARCHAR} and doc_id!=0 and course_id= #{course_id,jdbcType=VARCHAR}")
    int findMyAllDocCountByName(@Param("learner_phone")String learner_phone,@Param("course_id")int course_id);

    //查询学习者视频(课程名  模糊查询)
    @Select("select course_id, video_jd  from studing where learner_phone =#{learner_phone,jdbcType=VARCHAR} and video_id!=0 and course_id= #{course_id,jdbcType=VARCHAR}")
    List<Course> findMyAllVideoByName(@Param("learner_phone")String learner_phone,@Param("course_id")int course_id);

    //查询学习者文档(课程名  模糊查询)
    @Select("select course_id, doc_jd  from studing where learner_phone =#{learner_phone,jdbcType=VARCHAR} and doc_id!=0 and course_id= #{course_id,jdbcType=VARCHAR}")
    List<Course> findMyAllDocByName(@Param("learner_phone")String learner_phone,@Param("course_id")int course_id);
    //修改文档资源
    @Update("update doc  set course_dir= #{course_dir,jdbcType=VARCHAR} ,url= #{url,jdbcType=VARCHAR},nandu= #{nandu,jdbcType=VARCHAR} where course_name = #{course_name,jdbcType=VARCHAR}")
    void updateDoc(Course courserEntity);
    //修改视频资源
    @Update("update video set course_dir= #{course_dir,jdbcType=VARCHAR} ,url= #{url,jdbcType=VARCHAR},nandu= #{nandu,jdbcType=VARCHAR} where course_name =  #{course_name,jdbcType=VARCHAR}")
    void updateVideo(Course courserEntity);
    //查询是否存在大类
    @Select("select name from course_class where name = #{name,jdbcType=VARCHAR}")
    String findDirByname(String  name);

    //添加大类
    @Insert("insert into course_class (name) values (#{name,jdbcType=VARCHAR})")
    void addDir(String name);
    //修改大类
    @Update("update  course_class set name = #{course_dir,jdbcType=VARCHAR} where name=#{name,jdbcType=VARCHAR}")
    void updateDir(@Param("course_dir") String course_dir,@Param("name") String name);
    //查询所有学习大类
    @Select("select name from course_class")
    List<Course> findAllDir();

    //查询所有学习大类
    @Select("select count(*) from course_class")
    int findAllDirCount();
    //删除学习大类
    @Delete("Delete from course_class where name = #{name,jdbcType=VARCHAR}")
    void DeleteDir(@Param("name")String name);
    //查询大类ID
    @Select("select id from course_class where name = #{name,jdbcType=VARCHAR}")
    int findDirIDByname(String  name);

    //添加小类
    @Insert("insert into course (name,class) values (#{name,jdbcType=VARCHAR},#{id,jdbcType=VARCHAR})")
    void addCourse(@Param("name") String name,@Param("id") int id);


    //查询某大类下的小类条数
    @Select("select count(*) from course where class = #{id,jdbcType=VARCHAR}")
    int findAlCourseCount(int id);

    //查询某大类下的小类
    @Select("select name from course where class = #{id,jdbcType=VARCHAR}")
    List<Course> findAllCourse(int id);

    //查询是否存在小类别
    @Select("select name from course where name = #{name,jdbcType=VARCHAR}")
    String findCourseByname(@Param("name") String name);


    //删除小类
    @Delete("Delete from course where name = #{name,jdbcType=VARCHAR}")
    void DeleteCourse(@Param("name")String name);


    //修改小类
    @Update("update  course set name = #{course_name,jdbcType=VARCHAR},class= #{id,jdbcType=VARCHAR} where name= #{name,jdbcType=VARCHAR}")
    void updateCourse(@Param("course_name") String course_name,@Param("id")int id,@Param("name") String name);

}
